﻿using SqlSugar;

namespace WPFNote.Repository
{
    public class Category : RepositoryBasic<Models.Category, Models.CategorySch>, IDal.ICategory
    {
        /// <summary>
        /// 创建数据库表
        /// </summary>
        public void CreateTable()
         {
            // 创建表
            base.Context.CodeFirst.InitTables(
                //创建的四个表
                typeof(Models.Users),
                typeof(Models.Logs),
                typeof(Models.Category),
                typeof(Models.Note)
            );
        }

        /// <summary>
        /// 根据ID查询单个
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override async Task<Models.Category> SelectAsync(int id)
        {
            return await base.Context.Queryable<Models.Category>()
                .Where(c => c.Id == id)
                .OrderBy(st => st.Sort)
                .Mapper(c => c.Users, c => c.UserId, c => c.Users.Id)
                .FirstAsync();
        }

        /// <summary>
        /// 自定义条件单个查询
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public override async Task<Models.Category> SelectAsync(Models.CategorySch ts)
        {
            var (sql, sugarParameters) = Models.Utility.MySqlWhere.Sql(ts);
            return await base.Context.Queryable<Models.Category>()
                .Where(sql, sugarParameters)
                .OrderBy(st => st.Sort)
                .Mapper(c => c.Users, c => c.UserId, c => c.Users.Id)
                .FirstAsync();
        }

        /// <summary>
        /// 查询全部数据
        /// </summary>
        /// <returns></returns>
        public override async Task<List<Models.Category>> SelectListAsync()
        {
            return await base.Context.Queryable<Models.Category>()
                .OrderBy(st => st.Sort)
                .Mapper(c => c.Users, c => c.UserId, c => c.Users.Id)
                .ToListAsync();
        }
        /// <summary>
        /// 自定义条件查询列表
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public override async Task<List<Models.Category>> SelectListAsync(Models.CategorySch ts)
        {
            var (sql, sugarParameters) = Models.Utility.MySqlWhere.Sql(ts);
            return await base.Context.Queryable<Models.Category>()
                .Where(sql, sugarParameters)
                .OrderBy(st => st.Sort)
                .Mapper(c => c.Users, c => c.UserId, c => c.Users.Id)
                .ToListAsync();
        }


        public override async Task<List<Models.Category>> SelectListAsync(Models.CategorySch ts, int page, int size, RefAsync<int> total)
        {
            var (sql, sugarParameters) = Models.Utility.MySqlWhere.Sql(ts);
            return await base.Context.Queryable<Models.Category>()
                .Where(sql, sugarParameters)
                .OrderBy(st => st.Sort)
                .Mapper(c => c.Users, c => c.UserId, c => c.Users.Id)
                .ToPageListAsync(page, size, total);
        }

        /// <summary>
        /// 自定义条件查询列表-直接执行Sql语句-查询分类列表，并携带博客数量
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public async Task<List<Models.Category>> SelectListIncludeBlogNumAsync(Models.CategorySch ts)
        {
            var (whereSql, sugarParameters) = Models.Utility.MySqlWhere.Sql(ts);
            const string sql = "SELECT * FROM (select A.*,B.num AS BlogNum from Category A left JOIN (select CategoryId,COUNT(*) num FROM Blog GROUP BY CategoryId) B ON A.ID=B.CategoryId)T ";
            var sqlNew = sql + (string.IsNullOrWhiteSpace(whereSql) ? string.Empty : (" where " + whereSql)) + " order by sort";
            return await base.Context.Ado.SqlQueryAsync<Models.Category>(sqlNew, sugarParameters);
        }


        /// <summary>
        /// 自定义条件查询列表-直接执行Sql语句-查询分类列表，并携带笔记数量
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public async Task<List<Models.Category>> SelectListIncludeNoteNumAsync(Models.CategorySch ts)
        {
            var (whereSql, sugarParameters) = Models.Utility.MySqlWhere.Sql(ts);
            const string sql = "SELECT * FROM (select A.*,B.num AS NoteNum from Category A left JOIN (select CategoryId,COUNT(*) num FROM Note GROUP BY CategoryId) B ON A.ID=B.CategoryId)T ";
            var sqlNew = sql + (string.IsNullOrWhiteSpace(whereSql) ? string.Empty:(" where " + whereSql)) + " order by sort";
            return await base.Context.Ado.SqlQueryAsync<Models.Category>(sqlNew, sugarParameters);
        }
    }
}
